package info.javafans.dao;

import info.javafans.database.DatabaseManager;
import info.javafans.domain.Category;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CategoryDao {
	
	DatabaseManager dbManager = new DatabaseManager();
	
	/**
	 * 查找所有分类
	 * @return
	 */
	public List<Category> selectAllCategory() {

		List<Category> list = new ArrayList<Category>();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_category";
			ResultSet result = stmt.executeQuery(sql);

			while (result.next()) {
				Category category = new Category();
				
				category.setId(""+result.getInt("id"));
				category.setName(result.getString("name"));
				
				list.add(category);
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: CategoryDao-selectAllCategory-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return list;
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  CategoryDao-selectAllCategory");
				// e.printStackTrace();
			}
		}

		return null;
	}

	
	
	/**
	 * 按id查找分类
	 * 
	 * @param userId
	 */
	public Category selectCategoryById(String id) {

		Category category = new Category();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_category where id = " + id;
			ResultSet result = stmt.executeQuery(sql);

			if (result.next()) {
				category.setId(""+result.getInt("id"));
				category.setName(result.getString("name"));
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: CategoryDao-selectCategoryById-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return category;
			} catch (SQLException e) {
				System.out
						.println("info.javafans: CategoryDao-selectCategoryById-close conn");
				// e.printStackTrace();
			}
		}

		return null;
	}
	
	/**
	 * 添加category
	 * @param c
	 * @return
	 */
	public boolean addCategory(Category c) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "insert into jb_category(name) values('"+c.getName()+"')";
			int result = stmt.executeUpdate(sql);

			if (result == 1) {
				return true;
			}
			else {
				return false;
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: CategoryDao-addCategory-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn!=null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans: CategoryDao-addCategory-close conn");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * 更新category
	 * @param c
	 * @return
	 */
	public boolean updateCategory(Category c) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "update jb_category set name ='"+c.getName()+"' where id = "+c.getId();
			int result = stmt.executeUpdate(sql);

			if (result == 1) {
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("info.javafans: CategoryDao-updateCategory-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn!=null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans: CategoryDao-updateCategory-close conn");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * 删除category，按Id
	 * @param c
	 * @return
	 */
	public boolean deleteCategoryById(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "delete from jb_category where id = "+id;
			int result = stmt.executeUpdate(sql);

			if (result == 1) {
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("info.javafans: CategoryDao-deleteCategoryById-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn!=null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans: CategoryDao-deleteCategoryById-close conn");
				// e.printStackTrace();
			}
		}

		return false;
	}
}
